﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for ManagementPayment
/// </summary>
public class ManagementPayment
{
    SqlConnection coon;
	public ManagementPayment()
	{
		//
		// TODO: Add constructor logic here
		//
        coon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
	}
    /*********** PAYMENT METHOD ***********/

    public DataSet GetPayment()
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        DataSet dt = new DataSet();
        string sql = "select * from PaymentMethod,SubCategory where SubCategory.SubCategoryID=PaymentMethod.SubCategoryID order by PaymentMethod.PaymentMethodID DESC";
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);
        return dt;
    }

    public bool DeletePayment(int id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql1 = "select * from PaymentMethodDetail where PaymentMethodID = " + id + "";
        SqlCommand cmd1 = new SqlCommand(sql1, coon);
        SqlDataReader sda1 = cmd1.ExecuteReader();
        if (sda1.HasRows)
        {
            sda1.Close();
            return false;
        }
        sda1.Close();


        string sql3 = "select * from [Contract] where PaymentMethodID = " + id + "";
        SqlCommand cmd3 = new SqlCommand(sql3, coon);
        SqlDataReader sda3 = cmd3.ExecuteReader();
        if (sda3.HasRows)
        {
            sda3.Close();
            return false;
        }
        sda3.Close();


        string sql = "DELETE FROM  PaymentMethod where PaymentMethodID=" + id + "";
        SqlCommand cmd = new SqlCommand(sql, coon);
        int i = cmd.ExecuteNonQuery();

        coon.Close();
        return i > 0 ? true : false;
    }

    public DataSet SearchPayment(string s)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "select * from PaymentMethod,SubCategory where PaymentMethod.PaymentName like '%" + s + "%'and SubCategory.SubCategoryID=PaymentMethod.SubCategoryID order by PaymentMethod.PaymentMethodID DESC";
        DataSet dt = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);

        return dt;
    }


    public bool UpdatePaymentMethod(int id, string name, double priceRental, string note, int subCategoryID)
    {
        if (coon.State == ConnectionState.Closed)
        { coon.Open(); }

        string sql = "update PaymentMethod set PaymentName='" + name + "',PriceRental='" + priceRental + "',Note='" + note + "',[SubCategoryID]='" + subCategoryID + "' where PaymentMethodID = " + id + " ";
        SqlCommand cmd = new SqlCommand(sql, coon);

        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;
    }

    public ORANGE_PROJECTModel.PaymentMethod GetPayment(int id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        DataSet dt = new DataSet();
        string sql = "select * from PaymentMethod,SubCategory where PaymentMethod.PaymentMethodID=" + id + "  and SubCategory.SubCategoryID=PaymentMethod.SubCategoryID order by PaymentMethod.PaymentMethodID DESC";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sda = cmd.ExecuteReader();
        ORANGE_PROJECTModel.PaymentMethod p = new ORANGE_PROJECTModel.PaymentMethod();
        while (sda.Read())
        {
            p.PaymentMethodID = sda.GetInt32(0);
            p.PaymentName = sda.GetString(1);
            p.PriceRental = sda.GetDouble(2);
            p.Note = sda.GetString(3);
            p.SubCategoryID = sda.GetInt32(4);
        }


        return p;
    }
    public DataSet GetSubCategory()
    {
        DataSet dt = new DataSet();
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "SELECT * FROM SubCategory";
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);
        return dt;

    }


    public List<int> GetPaymentMethodID()
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        List<int> l = new List<int>();

        string sql = "select PaymentMethodID FROM PaymentMethod";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            l.Add(sdr.GetInt32(0));
        }
        sdr.Close();
        coon.Close();
        return l;
    }

    public bool AddPaymentMethod(string name, double PriceRental, string note, int subCategoryID)
    {

        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "insert into PaymentMethod values(@n,@p,@nt,@s)";
        SqlCommand cmd = new SqlCommand(sql, coon);
        cmd.Parameters.AddWithValue("@n", name);
        cmd.Parameters.AddWithValue("@p", PriceRental);
        cmd.Parameters.AddWithValue("@nt", note);
        cmd.Parameters.AddWithValue("@s", subCategoryID);
        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;


    }

    public object GetPaymentMethod()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM PaymentMethod Join SubCategory on SubCategory.SubCategoryID = PaymentMethod.SubCategoryID WHERE SubCategory.ServiceCategoryID=1 AND Paymentmethod.SubCategoryID=1", coon);
        da.Fill(ds);
        return ds;
    }
    public DataSet GetPaymentMethodBySubCategoryID(int id)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM PaymentMethod WHERE SubCategoryID=" + id, coon);
        da.Fill(ds);
        return ds;
    }
}